package com.hitrust.gs.dao;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import system.base.Provider;

import com.hitrust.gs.bean.GSProviderBean;
import com.hitrust.gs.lib.GSLogWriter;
import com.hitrust.gs.lib.SQLLogger;
import com.hitrust.gs.lib.db.GSBaseDAO;
import com.hitrust.gs.lib.db.GSPreparedStatementFactory;
import com.hitrust.gs.service.GSBaseBean;
import com.hitrust.gs.util.exception.GSException;
/**
 * 处理招标分类对应供应商和仓库，限制仓库申购时选择某类的产品必须使用对应的供应商
 * @author wang
 *
 */
public class GSOrderCategoryDAO extends GSBaseDAO {
    /**
     * 查询招标分类下的供应商
     * @param aProviderId           招标分类编码
     * @return                      供应商数据对�象
     * @throws GSException
     */
    public List queryProviderByBid(String aProviderId) throws GSException{
    	List ls = null;
        String tSql = "select * from t_PROVIDER where code in ( select distinct provider_code from t_product_bid where status=1 and product_type=?)";
        Object tObj[] = {aProviderId};
        try{
            ls = executeQuery(tSql,tObj);          
        }catch(Exception e){
            GSLogWriter.error(this, e.toString());
            throw new GSException(GSException.E19999, GSException.E19999_MSG);
        }
        return ls;
    }
    /**
     * 查询招标分类下的供应商
     * @param aProviderId           招标分类编码
     * @return                      供应商数据对�象
     * @throws GSException
     */
    public List queryProviderByOrderCategory(String aProviderId,String warehouseCode) throws GSException{
    	List ls = null;
        String tSql = "select * from t_PROVIDER where code in ( select  provider_code from t_order_category where product_type=? and warehouse_code=?  )";
        Object tObj[] = {aProviderId,warehouseCode};
        try{
            ls = executeQuery(tSql,tObj);         
        }catch(Exception e){
            GSLogWriter.error(this, e.toString());
            throw new GSException(GSException.E19999, GSException.E19999_MSG);
        }
        return ls;
    }
    /**
     * 查询是否是仓库招标分类下的供应商
     * @param aProviderId           招标分类编码
     * @return                      供应商数据对�象
     * @throws GSException
     */
    public boolean queryProductByOrderCategory(String productCode,String providerCode,String warehouseCode) throws GSException{
    	boolean yes=true;
        String tSql = " select * from t_order_category where product_type in (" +
        		"select product_type from t_product_bid where product_code=? and status=1 and provider_code=? ) and warehouse_code=? and provider_code=? ";
        Object tObj[] = {productCode,providerCode,warehouseCode,providerCode};
        try{
        	yes = executeList(tSql,tObj);
        }catch(Exception e){
            GSLogWriter.error(this, e.toString());
            throw new GSException(GSException.E19999, GSException.E19999_MSG);
        }
        return yes==true?false:true;
    }
    /**
     * 查询是否是招标分类下的供应商
     * @param aProviderId           招标分类编码
     * @return                      供应商数据对�象
     * @throws GSException
     */
    public boolean queryProductCategory(String productCode,String providerCode) throws GSException{
    	boolean yes=false;
        String tSql = " select  * from t_order_category where product_type in (" +
        		"select product_type from t_product_bid where product_code=? and status=1 and provider_code=? ) ";
        Object tObj[] = {productCode,providerCode};
        try{
        	yes = executeList(tSql,tObj);
        }catch(Exception e){
            GSLogWriter.error(this, e.toString());
            throw new GSException(GSException.E19999, GSException.E19999_MSG);
        }
        return yes;
    }
    /**
     * 删除记录
     * 
     * @author owen
     * 
     * @param aProviderId 供应商编号
     *           
     */
    public boolean delete(String product_type,String warehouseCode) throws GSException {
        String tSql = "delete from  t_order_category where warehouse_code= ? and product_type=?";
        Object[] tObject = {warehouseCode,product_type};
        boolean tResult = false;
        try {
            tResult=executeUpdate(tSql, tObject);
        } catch (Exception ex) {
            GSLogWriter.error(this, ex.getMessage());
            throw new GSException(ex.getMessage());
        }

        return tResult;
    }
    /**
     * 新增一笔记录
     * @author owen
     * @param aGSProviderBean
     * 
     * return boolean
     * 
     * @throws GSException
     */
    public boolean add(String warehouseCode,String providerCode,String product_type) throws GSException {

        String tSql="INSERT INTO t_order_category "+
                "VALUES(sys_guid(),?,?,?)";

        Object[] tObject = new Object[3] ;
          tObject[0]=warehouseCode;
          tObject[1]=providerCode;
          tObject[2]=product_type;
        boolean tResult = false;
        try {
            tResult = executeUpdate(tSql, tObject);

        } catch (Exception ex) {
            GSLogWriter.error(this, ex.toString());
            throw new GSException(ex.getMessage());
        }

        return tResult;
    }
	public ArrayList executeQuery( String aSql,
			Object[] aStmntValues) throws SQLException, Exception {
		try {
			iConn = this.getConnetion();
			SQLLogger.log(aSql, aStmntValues);
			iPstmt = iConn.prepareStatement(aSql);

			// 组合Statement条件.
			if (aStmntValues != null) {
				GSPreparedStatementFactory.buildStatement(iPstmt, aStmntValues);
			}

			// 执行
			iRs = iPstmt.executeQuery();

			ArrayList tArrayList = new ArrayList();
			while (iRs.next()) {
				Provider aBean= new Provider();
				aBean.initObject(iRs);
				tArrayList.add(aBean);
			}
			SQLLogger.log("Success");
			// 返回结果数组列表
			return tArrayList;
		} catch (SQLException e) {
			SQLLogger.log("error:" + e.toString());
			pushSQLError(e.toString());
			GSLogWriter.debug(this, e);
			GSLogWriter.debug(this, aSql);
			throw e;
		} finally {
			this.close();
		}
	}
	public boolean executeList( String aSql,
			Object[] aStmntValues) throws SQLException, Exception {
		boolean flag=false;
		try {
			iConn = this.getConnetion();
			SQLLogger.log(aSql, aStmntValues);
			iPstmt = iConn.prepareStatement(aSql);

			// 组合Statement条件.
			if (aStmntValues != null) {
				GSPreparedStatementFactory.buildStatement(iPstmt, aStmntValues);
			}

			// 执行
			iRs = iPstmt.executeQuery();
			if(iRs.next()){
				flag=true;
			}
			SQLLogger.log("Success");
			// 返回结果数组列表
			
		} catch (SQLException e) {
			SQLLogger.log("error:" + e.toString());
			pushSQLError(e.toString());
			GSLogWriter.debug(this, e);
			GSLogWriter.debug(this, aSql);
			throw e;
		} finally {
			this.close();
		}
		return flag;
	}
	
	public ArrayList executeQuery2( String aSql,
			Object[] aStmntValues) throws SQLException, Exception {
		try {
			iConn = this.getConnetion();
			SQLLogger.log(aSql, aStmntValues);
			iPstmt = iConn.prepareStatement(aSql);

			// 组合Statement条件.
			if (aStmntValues != null) {
				GSPreparedStatementFactory.buildStatement(iPstmt, aStmntValues);
			}

			// 执行
			iRs = iPstmt.executeQuery();

			ArrayList tArrayList = new ArrayList();
			while (iRs.next()) {
				//Provider aBean= new Provider();
				//aBean.initObject(iRs);
				String brand = iRs.getString("product_brand");
				tArrayList.add(brand);
			}
			SQLLogger.log("Success");
			// 返回结果数组列表
			return tArrayList;
		} catch (SQLException e) {
			SQLLogger.log("error:" + e.toString());
			pushSQLError(e.toString());
			GSLogWriter.debug(this, e);
			GSLogWriter.debug(this, aSql);
			throw e;
		} finally {
			this.close();
		}
	}
	
	/**
     * 查询品牌
     * @throws GSException
     */
    public List queryProductBrand() throws GSException{
    	String providerCode = dateProperties();
    	List ls = null;
        String tSql = "select product_brand from t_product a1 where a1.provider_code = ? and a1.status='1' and a1.is_buy='1' group by a1.product_brand";
        Object tObj[] = {providerCode};
        try{
            ls = executeQuery2(tSql,tObj);          
        }catch(Exception e){
            GSLogWriter.error(this, e.toString());
            throw new GSException(GSException.E19999, GSException.E19999_MSG);
        }
        return ls;
    }
    
    /**
     * 查询选中仓库 选中的品牌
     * @return 
     * @throws GSException
     */
    public List queryProductBrandCheck(String warehouseCode) throws GSException{
    	List ls = null;
        String tSql = "select product_brand from t_w_p_center_brand where warehouse_code=?";
        Object tObj[] = {warehouseCode};
        try{
            ls = executeQuery2(tSql,tObj);         
        }catch(Exception e){
            GSLogWriter.error(this, e.toString());
            throw new GSException(GSException.E19999, GSException.E19999_MSG);
        }
        return ls;
    }
    
    /**
     * 删除记录
     * 
     *           
     */
    public boolean delete2(String warehouseCode) throws GSException {
        String tSql = "delete from  t_w_p_center_brand where warehouse_code= ?";
        Object[] tObject = {warehouseCode};
        boolean tResult = false;
        try {
            tResult=executeUpdate(tSql, tObject);
        } catch (Exception ex) {
            GSLogWriter.error(this, ex.getMessage());
            throw new GSException(ex.getMessage());
        }

        return tResult;
    }
    
    /**
     * 新增一笔记录
     */
    public boolean add2(String warehouseCode,String BrandName) throws GSException {

        String tSql="INSERT INTO t_w_p_center_brand "+
                "VALUES(sys_guid(),?,?)";

        Object[] tObject = new Object[2] ;
          tObject[0]=warehouseCode;
          tObject[1]=BrandName;
        boolean tResult = false;
        try {
            tResult = executeUpdate(tSql, tObject);

        } catch (Exception ex) {
            GSLogWriter.error(this, ex.toString());
            throw new GSException(ex.getMessage());
        }

        return tResult;
    }
    
    
    /**
     * 查询  所有仓库是否有开启 品牌关联数据验证
     */
    public boolean queryProductBrandOfHave() throws GSException{
    	String providerCode = dateProperties();
    	boolean yes=true;
        String tSql = " select * from t_w_p_center_brand";
        if(providerCode==null){
        	return false;
        }
        Object tObj[] = null;
        try{
        	yes = executeList(tSql,tObj);
        }catch(Exception e){
            GSLogWriter.error(this, e.toString());
            throw new GSException(GSException.E19999, GSException.E19999_MSG);
        }
        return yes;
    }
    
    
    /**
     * 查询产品是否是指定供应商  产品
     *          
     */
    public boolean queryProductBrandIsCenter(String productCode) throws GSException{
    	String providerCode = dateProperties();  
        boolean yes=true;
        String tSql = "select * from t_product a1 where a1.concentricity_code = ? and a1.provider_code= ? ";
        Object tObj[] = {productCode,providerCode};
        try{
        	yes = executeList(tSql,tObj);
        }catch(Exception e){
            GSLogWriter.error(this, e.toString());
            throw new GSException(GSException.E19999, GSException.E19999_MSG);
        }
        return yes;
        
    }
    
    
    /**
     * 查询  仓库 中心库 分配 品牌
     */ 
    public boolean queryProductBrand(String productCode,String warehouseCode) throws GSException{
    	boolean yes=true;
        String tSql = " select * from t_w_p_center_brand where product_brand in (select product_brand from t_product where concentricity_code=?) and warehouse_code=? ";
        Object tObj[] = {productCode,warehouseCode};
        try{
        	yes = executeList(tSql,tObj);
        }catch(Exception e){
            GSLogWriter.error(this, e.toString());
            throw new GSException(GSException.E19999, GSException.E19999_MSG);
        }
        return yes==true?false:true;
    }
    /**
     *   读写date.properties 里的中心库编码值
     */
    public String dateProperties(){
    	Properties prop = new Properties();
    	String providerCode = "";
			try {
				String path = Thread.currentThread().getContextClassLoader().getResource("date.properties").getPath();
				FileInputStream fis = new FileInputStream(path);
				try {
					prop.load(fis);
					providerCode = prop.getProperty("center.providerCode");
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			} catch (FileNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		
    	return providerCode;
    }
    
}
